# LuckPay Web Manage System
#
# Copyright (c) 2016 Lucky Byte, Inc.
#
express = require 'express'
pgsql   = require 'pg'
moment  = require 'moment'
fs      = require 'fs'
mkdirp  = require 'mkdirp'
path    = require 'path'
router = express.Router()
module.exports = router


router.all '/', (req, res, next) ->
    res.locals.stat_title = '助农取款代付汇总'

    today = moment().format('YYYY-MM-DD')

    # 如果没有提供日期，则仅查询当日
    res.locals.start_date = req.body.start_date or \
        req.query.start_date or today
    res.locals.end_date = req.body.end_date or \
        req.query.end_date or today

    table_name = 'pay_p2_order_hist'
    trlist_table = 'pay_trlist_hist'
    if res.locals.start_date is today and res.locals.end_date is today
        table_name = 'pay_p2_order'
        trlist_table = 'pay_trlist'

    where_clause = """
        o.sys_time::date >= '#{res.locals.start_date}'
        and o.sys_time::date <= '#{res.locals.end_date}'
    """
    res.locals.total = 1

    await pgsql.connect settings.pgsql_url, defer err, client, done
    if err then done(client); return next(err)

    # 查询消费统计
    await client.query \
        "select count(o.*) as count,
            case when t.chnl in (1,4) then
                sum(cast(json_extract_path_text(i_pkt,'4') as decimal)) / 100
            end as amt
        from #{table_name} as o
        left join #{trlist_table} as t on
            t.uuid = o.t60_uuid and t.uuid_no = 1
        where #{where_clause}
        group by t.chnl", defer err, result
    if err then done(client); return next(err)
    res.locals.t60_stats = result.rows[0] or {}

    # 尚未代付统计
    await client.query \
        "select count(o.*) as count,
            case when t.chnl in (1,4) then
                sum(cast(json_extract_path_text(i_pkt,'4') as decimal)) / 100
            end as amt
            from #{table_name} as o
            left join #{trlist_table} as t on
                t.uuid = o.t60_uuid and t.uuid_no = 1
            where #{where_clause} and o.status = 1
            group by t.chnl", defer err, result
    if err then done(client); return next(err)
    res.locals.t40_status_1 = result.rows[0] or {}

    # 尚未确认统计
    await client.query \
        "select count(o.*) as count,
            case when t.chnl = 3 then
                sum(cast(json_extract_path_text(i_pkt,'txnAmt') as decimal)) / 100
            end as amt
            from #{table_name} as o
            left join #{trlist_table} as t on
                t.uuid = o.t40_uuid and t.incoming = false and
                json_extract_path_text(t.trinfo, 'code')::text = 't40'
            where #{where_clause} and o.status = 2
            group by t.chnl", defer err, result
    if err then done(client); return next(err)
    res.locals.t40_status_2 = result.rows[0] or {}

    # 代付失败统计
    await client.query \
        "select count(o.*) as count,
            case when t.chnl = 3 then
                sum(cast(json_extract_path_text(i_pkt,'txnAmt') as decimal)) / 100
            end as amt
            from #{table_name} as o
            left join #{trlist_table} as t on
                t.uuid = o.t40_uuid and t.incoming = false and
                json_extract_path_text(t.trinfo, 'code')::text = 't40'
            where #{where_clause} and o.status = 3
            group by t.chnl", defer err, result
    if err then done(client); return next(err)
    res.locals.t40_status_3 = result.rows[0] or {}

    # 代付成功统计
    await client.query \
        "select count(o.*) as count,
            case when t.chnl = 3 then
                sum(cast(json_extract_path_text(i_pkt,'txnAmt') as decimal)) / 100
            end as amt,
            ( select sum(p2_fee) from #{table_name}
                where status = 4 and fee_take_from = 1 and
                    sys_time::date >= '#{res.locals.start_date}' and
                    sys_time::date <= '#{res.locals.end_date}'
            ) as fee_from_1,
            ( select sum(p2_fee) from #{table_name}
                where status = 4 and fee_take_from = 2 and
                    sys_time::date >= '#{res.locals.start_date}' and
                    sys_time::date <= '#{res.locals.end_date}'
            ) as fee_from_2
            from #{table_name} as o
            left join #{trlist_table} as t on
                t.uuid = o.t40_uuid and t.incoming = false and
                json_extract_path_text(t.trinfo, 'code')::text = 't40'
            where #{where_clause} and o.status = 4
            group by t.chnl", defer err, result
    if err then done(client); return next(err)
    res.locals.t40_status_4 = result.rows[0] or {}
    done()
    res.render 'trstat/p2_order_sum'
